package tms.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

public final class DbManager {

    public static final String DATE_FORMAT = "yyyy-MM-dd HH:mm";

    private DbManager() {
    }

    public static void main(String[] args) {
        // TODO
    }

    private static Connection connection;

    private static Statement statement;

    public static Connection getConnection() {
        return connection;
    }

    /**
     * Create a connection to SQLite DB "TravelSystem.db".
     *
     * @return corresponding Statement if success, otherwise null
     */
    static void openConnection() {
        try {
            if (connection == null) {

                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite:TravelSystem.db");
                statement = connection.createStatement();
            }
        } catch (Exception e) {
            JOptionPane.showConfirmDialog(null,
                    "Error on opening database connection");
        }
    }

    static void closeConnection() {
        if (connection != null) {
            try {

                if (statement != null) {
                    statement.close();
                    statement = null;
                }
                connection.close();
                connection = null;
            } catch (SQLException e) {
                JOptionPane.showConfirmDialog(null, e.getMessage());
            }
        }
    }

    /**
     * Create a default DB Attention: this method will drop all existed tables.
     */
    void createDb() {
        try {
            // OpenConnection();
            //
            // statement.executeUpdate("drop table if exists Flights");
            //
            // statement.executeUpdate("CREATE TABLE Flights (Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,Departing text,Arriving text,DepartureDate date,TotalSeats integer, FlightName text, IsActive boolean DEFAULT true, Discount double)");
            // statement.executeUpdate("INSERT INTO Flights(Departing,Arriving,DepartureDate,TotalSeats,FlightName, IsActive, Discount) "
            // +
            // "values('HCM', 'HN', date('now'), 80, 'F002', 1, 10)");
            // statement.executeUpdate("INSERT INTO Flights(Departing,Arriving,DepartureDate,TotalSeats,FlightName, IsActive, Discount) "
            // +
            // "values('HN', 'HCM', date('now'), 90, 'F002', 1, 0)");
            //
            // statement.executeUpdate("drop table if exists Account");
            // statement.executeUpdate("CREATE TABLE Account (Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
            // +
            // "FirstName text,LastName text,Email text,PIDNumber text,Address text,Phone text,"
            // +
            // "IsActive boolean DEFAULT true,FFNumber text,FFScore int, RegistrationDate date,"
            // +
            // "Password text,Username text,City text,Province text,Country text,PassportNumber text)");
            // statement.executeUpdate("INSERT INTO Account(FirstName,LastName,Email,PIDNumber,Address, Phone, IsActive, FFNumber,FFScore,RegistrationDate,Password,Username,City,Province,Country,PassportNumber ) "
            // +
            // "values('John', 'Cena', 'john@abc.com', '123456', '', '+33', 1, 'FF001', 0, date('now','localtime'),'123456','johncena','LA','LA','USA','ppn1');");
            // statement.executeUpdate("INSERT INTO Account(FirstName,LastName,Email,PIDNumber,Address, Phone, IsActive, FFNumber,FFScore,RegistrationDate,Password,Username,City,Province,Country,PassportNumber ) "
            // +
            // "values('Tom', 'Cruise', 'Tom@abc.com', '123456', '', '+33', 1, 'FF002', 0, date('now','localtime'),'123456','tomcruise','LA','LA','USA','ppn1');");
            // statement.executeUpdate("INSERT INTO Account(FirstName,LastName,Email,PIDNumber,Address, Phone, IsActive, FFNumber,FFScore,RegistrationDate,Password,Username,City,Province,Country,PassportNumber ) "
            // +
            // "values('Paul', 'Smith', 'Paul@abc.com', '123456', '', '+33', 1, 'FF003', 0, date('now','localtime'),'123456','paulsmith','LA','LA','USA','ppn1');");
            // statement.executeUpdate("INSERT INTO Account(FirstName,LastName,Email,PIDNumber,Address, Phone, IsActive, FFNumber,FFScore,RegistrationDate,Password,Username,City,Province,Country,PassportNumber ) "
            // +
            // "values('Marry', 'Jane', 'Marry@abc.com', '123456', '', '+33', 1, 'FF004', 0, date('now','localtime'),'123456','marryjane','LA','LA','USA','ppn1');");
        } catch (Exception e) {
            JOptionPane.showConfirmDialog(null, e.getMessage());
        } finally {
            closeConnection();
        }
    }

    // Test loading database, don't use this
    void printAccounts() {
        try {
            openConnection();

            ResultSet rs = statement.executeQuery("SELECT * FROM Account;");
            while (rs.next()) {
                System.out.println(rs.getString("FirstName") + " "
                        + rs.getString("LastName"));
            }
        } catch (Exception e) {
            JOptionPane.showConfirmDialog(null, e.getMessage());
        } finally {
            closeConnection();
        }
    }

    private static String extractWhereClause(String where) {
        String whereClause = "1=1";
        if (!"".equals(where.trim())) {
            whereClause += " AND " + where;
        }

        return whereClause;
    }

    private static String extractAssignment(String[] columns, String[] values) {
        String assignment = "";
        for (int i = 0; i < columns.length; i++) {
            assignment += columns[i] + "=" + values[i] + ",";
        }

        return assignment.substring(0, assignment.length() - 1);
    }

    private static String extractFields(List<String> fields) {
        String result = "";
        for (String field : fields) {
            result += field + ",";
        }

        return result.substring(0, result.length() - 1);
    }

    /**
     * Get one Object corresponding to the where clause.
     *
     * @param tableName
     *            should not be empty
     * @param where
     *            condition to perform
     * @return a map containing all fields of the expected object, otherwise
     *         null
     */
    public static Map<String, Object> getFirstDynamic(String tableName,
            String where) {
        try {
            openConnection();

            String query = "SELECT * FROM " + tableName + " WHERE "
                    + extractWhereClause(where) + " ORDER BY Id LIMIT 1;";

            ResultSet rs = statement.executeQuery(query);
            Map<String, Object> map = new Hashtable<String, Object>();
            ResultSetMetaData rsmt = rs.getMetaData();
            for (int i = 1; i <= rsmt.getColumnCount(); i++) {
                String column = rsmt.getColumnName(i).toLowerCase();
                map.put(column, rs.getObject(column));
            }

            return map;
        } catch (Exception e) {
            return null;
        } finally {
            closeConnection();
        }
    }

    /**
     * Get a list of Object corresponding to the where clause.
     *
     * @param tableName
     *            should not be empty
     * @param where
     *            condition to perform
     * @return list of maps containing all fields of expected objects, otherwise
     *         return an empty list (size = 0)
     */
    public static List<Map<String, Object>> getListDynamic(String tableName,
            String where) {
        try {
            openConnection();

            String query = "SELECT * FROM " + tableName + " WHERE "
                    + extractWhereClause(where) + ";";

            List<Map<String, Object>> lsResult = new ArrayList<Map<String, Object>>();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()) {
                Map<String, Object> map = new Hashtable<String, Object>();
                ResultSetMetaData rsmt = rs.getMetaData();
                for (int i = 1; i <= rsmt.getColumnCount(); i++) {
                    String column = rsmt.getColumnName(i).toLowerCase();
                    map.put(column, rs.getObject(column));
                }
                lsResult.add(map);
            }

            return lsResult;
        } catch (Exception e) {
            return new ArrayList<Map<String, Object>>();
        } finally {
            closeConnection();
        }
    }

    /**
     * Insert a new Object.
     *
     * @param tableName
     *            should not be empty
     * @param columns
     *            all columns of the table except Id column
     * @param values
     *            all values to be inserted
     * @return true if success otherwise false
     */
    public static boolean insert(String tableName, String[] columns,
            String[] values) {
        try {
            openConnection();

            List<String> lsColumns = new ArrayList<String>();
            List<String> lsValues = new ArrayList<String>();

            for (int i = 0; i < values.length; i++) {
                if (!"".equals(values[i].trim())
                        && values[i].trim() != new String("''")) {
                    lsColumns.add(columns[i]);
                    lsValues.add(values[i]);
                }
            }

            String query = "INSERT INTO " + tableName + "("
                    + extractFields(lsColumns) + ") VALUES ("
                    + extractFields(lsValues) + ");";

            return statement.executeUpdate(query) > 0;
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }
    }

    /**
     * Update an Object.
     *
     * @param tableName
     *            should not be empty
     * @param columns
     *            all columns of the table except Id column
     * @param values
     *            all values to be updated
     * @param where
     *            condition of update
     * @return true if success otherwise false
     */
    public static boolean update(String tableName, String[] columns,
            String[] values, String where) {
        try {
            openConnection();

            String query = "UPDATE " + tableName + " SET "
                    + extractAssignment(columns, values) + " WHERE "
                    + extractWhereClause(where) + ";";

            return statement.executeUpdate(query) > 0;
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }
    }

    public static boolean delete(String tableName, String where) {
        try {
            openConnection();

            String query = "DELETE FROM " + tableName + " WHERE "
                    + extractWhereClause(where) + ";";

            return statement.executeUpdate(query) > 0;
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }
    }

    /**
     * Execute a dynamic query: UPDATE, SELECT or INSERT.
     *
     * @param query
     *            Ex: UPDATE account SET IsActive = 1 WHERE account.name LIKE
     *            '%superstar%'
     */
    public static void execute(String query) {
        try {
            openConnection();
            statement.execute(query);
        } catch (Exception e) {
            JOptionPane.showConfirmDialog(null, e.getMessage());
        } finally {
            closeConnection();
        }
    }

}
